//
//  LYDb.m
//  LeYou_demo
//
//  Created by naffan on 12-11-20.
//  Copyright (c) 2012年 naffan. All rights reserved.
//

#import "LYDb.h"

@implementation LYDb

//创建数据库
-(BOOL)create{
    //db = [self open];
    Boolean result = [db executeUpdate:@"create table if not exists note (id integer primary key autoincrement,username text,lastlogintime date,loginflag integer,ordernum integer)"];
    if (result == YES) {
        NSLog(@"create OK!");
        return YES;
    }else{
        NSLog(@"create fail!");
        return NO;
    }    
}

//打开数据库
-(FMDatabase*)open{
    NSArray *paths= NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    NSString *path = [documentsDirectory stringByAppendingPathComponent:DBName];
    //NSLog(@"%@",path);
    NSFileManager *fileManager = [NSFileManager defaultManager];
    BOOL find = [fileManager fileExistsAtPath:path];
    if(find){
        db= [FMDatabase databaseWithPath:path] ;
        [db open];
        NSLog(@"[open] 数据库%@已经存在",DBName);
        return db;
    }else{
        db= [FMDatabase databaseWithPath:path] ;
        if (![db open]) {
            NSLog(@"[open] 数据库%@创建失败",DBName);
            return nil;
        }else{
            NSLog(@"[open] 数据库%@创建成功",DBName);
            return db;
        }
    }
}


-(BOOL)insert:(NSString*)username loginflag:(NSInteger*)loginflag ordernum:(NSInteger*)ordernum
{
    db = [self open];
    Boolean result =[db executeUpdate:[NSString stringWithFormat:@"insert into note (username, lastlogintime, loginflag,ordernum)values ('%@','%@','%d','%ld')",username,[NSDate dateWithTimeIntervalSinceNow: +(8*60*60)],loginflag,ordernum ]];
    NSLog(@"insert sql: %@",[NSString stringWithFormat:@"insert into note (username, lastlogintime, loginflag,ordernum)values ('%@','%@','%d','%d')",username,[NSDate dateWithTimeIntervalSinceNow: +(8*60*60)],loginflag,ordernum ]);
    if (result == YES) {
        NSLog(@"insert succ!");
        return YES;
    }else{
        NSLog(@"insert fail!");
        return NO;
    }
    /*
    //定义一个sql语句
    NSString *insertStatementNS =[NSString stringWithFormat:@"insert into note (username, lastlogintime, loginflag,ordernum)values ('%d','%@','%d','%d')",username,[NSDate dateWithTimeIntervalSinceNow: +(8*60*60)],loginflag,ordernum ];
    NSLog(@"%@",insertStatementNS);
    //将定义的NSString的sql语句，转换成UTF8的c风格的字符串
    const char *insertSql = [insertStatementNS UTF8String];
    //执行插入语句
    if (sqlite3_exec(database, insertSql, NULL, NULL, &errorMsg)==SQLITE_OK) {
        NSLog(@"insert ok.");
        return YES;
    }else{
        NSLog(@"error: %s",errorMsg);
        sqlite3_free(errorMsg);
    }
    return NO;
    */
}

-(NSMutableDictionary*)selectAll{
    NSMutableDictionary *data = [[NSMutableDictionary alloc]init];
    NSString *sql =@"select * from userinfo";
    const char *selectSql = [sql UTF8String];
    if (sqlite3_prepare_v2(database, selectSql, -1, &statement, nil)==SQLITE_OK) {
        while (sqlite3_step(statement)== SQLITE_ROW) {
            
            char *field1 = (char *)sqlite3_column_text(statement, 0);
            NSString *field1Str = [[NSString alloc]initWithUTF8String:field1];
            
            char *field2 = (char *)sqlite3_column_text(statement, 1);
            NSString *field2Str = [[NSString alloc]initWithUTF8String:field2];
            
            char *field3 = (char *)sqlite3_column_text(statement, 2);
            NSString *field3Str = [[NSString alloc]initWithUTF8String:field3];
            
            char *field4 = (char *)sqlite3_column_text(statement, 3);
            NSString *field4Str = [[NSString alloc]initWithUTF8String:field4];
            
            char *field5 = (char *)sqlite3_column_text(statement, 4);
            NSString *field5Str = [[NSString alloc]initWithUTF8String:field5];
            

            [data setObject:field1Str forKey:@"id"];
            [data setObject:field2Str forKey:@"username"];
            [data setObject:field4Str forKey:@"lastlogintime"];
            [data setObject:field3Str forKey:@"loginflag"];
            [data setObject:field5Str forKey:@"ordernum"];

            
            [field1Str release];
            [field2Str release];
            [field3Str release];
            [field4Str release];
            [field5Str release];
            
        }
        sqlite3_finalize(statement);
        NSLog(@"%@",data);
        return data;
    }
    return nil;
}

-(void)query
{
    rs=[db executeQuery:@"SELECT * FROM userinfo"];
    NSLog(@"[query ]%@",rs);
    while ([rs next]){
        NSLog(@"[query ] %@%@",[rs stringForColumn:@"id"],[rs stringForColumn:@"loginflag"]);
    }

}

-(BOOL)update:(NSString*)username loginflag:(NSInteger*)loginflag ordernum:(NSInteger*)ordernum
{
    /*
    Boolean result = [db executeUpdate:@"UPDATE note SET Name = ? WHERE Name = ? ",@"老婆",@"宝贝"];
    */
    //更新语句
    NSString *updateString=[NSString stringWithFormat:@"update note set loginflag='%d', lastlogintime='%@', ordernum='%d' where username=%@",loginflag,[NSDate dateWithTimeIntervalSinceNow: +(8*60*60)],ordernum,username];
    // NSLog(@"%@",aNote);
    const char *updateSql=[updateString UTF8String];
    //执行更新语句
    if(sqlite3_exec(database, updateSql, NULL, NULL, &errorMsg)==SQLITE_OK){
        NSLog(@"更新成功");
        return YES;
    }else{
        return NO;
    }
    
}

-(void)delete
{
    db = [self open];
    [db executeUpdate:@"DELETE FROM userinfo WHERE loginflag = 1"];
}

/************************************/

-(BOOL)create_back:(NSString*)sql
{
    db = [self open];
    Boolean result = [db executeUpdate:sql];
    if (result == YES) {
        NSLog(@"[create_back] 创建表语句执行成功!");
        return YES;
    }else{
        NSLog(@"[create_back] 创建表失败!");
        return NO;
    }
}

-(BOOL)insert_back:(NSString*)sql
{
    db = [self open];
    NSLog(@"insert_back sql:%@",sql);
    db = [self open];
    Boolean result =[db executeUpdate:sql];
    //NSString stringWithFormat:@"insert into note (username, lastlogintime, loginflag,ordernum)values ('%@','%@','%d','%d')",username,[NSDate dateWithTimeIntervalSinceNow: +(8*60*60)],loginflag,ordernum ]
    //NSLog(@"insert sql: %@",[NSString stringWithFormat:@"insert into note (username, lastlogintime, loginflag,ordernum)values ('%@','%@','%d','%d')",username,[NSDate dateWithTimeIntervalSinceNow: +(8*60*60)],loginflag,ordernum ]);
    if (result == YES) {
        NSLog(@"insert_back succ!");
        return YES;
    }else{
        NSLog(@"insert_back fail!");
        return NO;
    }
}

-(BOOL)update_back:(NSString*)sql
{
    db = [self open];
    NSLog(@"update_back sql:%@",sql);
    //更新语句
    NSString *updateString=sql;
    const char *updateSql=[updateString UTF8String];
    //执行更新语句
    if(sqlite3_exec(database, updateSql, NULL, NULL, &errorMsg)==SQLITE_OK){
        NSLog(@"更新成功");
        return YES;
    }else{
        return NO;
    }
    
}

-(NSString*)query_back:(NSString*)sql column:(NSString*)colunm
{
    db = [self open];
    NSLog(@"[query_back] query_back sql:%@",sql);
    db = [self open];
    rs=[db executeQuery:sql];
    while ([rs next]){
        return [rs stringForColumn:colunm];
    }
    return false;
}


@end
